package models

import (
	"database/sql"
	"log"
	"time"

	_ "github.com/mattn/go-sqlite3" // 导入 SQLite 驱动
)

var db *sql.DB               // 声明全局数据库连接
var dbPath = "./shopping.db" // 默认数据库路径

// SetDBPath 设置数据库路径
func SetDBPath(path string) {
	dbPath = path
}

// 初始化数据库连接
func InitDB() {
	var err error
	db, err = sql.Open("sqlite3", dbPath) // 连接到 SQLite 数据库
	if err != nil {
		log.Fatal(err)
	}

	// 设置数据库连接池
	db.SetMaxOpenConns(10)
	db.SetMaxIdleConns(5)
	db.SetConnMaxLifetime(time.Minute * 5)

	// 启用外键支持
	_, err = db.Exec("PRAGMA foreign_keys = ON;")
	if err != nil {
		log.Fatal("Failed to enable foreign keys:", err)
	}

	// 设置事务超时
	_, err = db.Exec("PRAGMA busy_timeout = 5000;")
	if err != nil {
		log.Fatal("Failed to set busy timeout:", err)
	}

	// 创建用户表
	createUserTableQuery := `
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT,
        email TEXT,
        password TEXT,
        phonenumber TEXT,
        verify_code TEXT
    );`
	_, err = db.Exec(createUserTableQuery)
	if err != nil {
		log.Fatal("Failed to create users table:", err)
	}

	// 创建收件人地址表
	createAddressTableQuery := `
	CREATE TABLE IF NOT EXISTS addresses (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		user_id INTEGER NOT NULL,
		addressee TEXT,
     	phonenumber TEXT,
		address TEXT NOT NULL,
		is_default INTEGER DEFAULT 0,
		
		FOREIGN KEY(user_id) REFERENCES users(id)
	);`
	_, err = db.Exec(createAddressTableQuery)
	if err != nil {
		log.Fatal("Failed to create addresses table:", err)
	}

	// 创建商品表
	// fromplace发货地, quantity库存, salesvolume销量, brand品牌, shop店家
	createProductTableQuery := `
    CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        productname TEXT NOT NULL,
		image_url TEXT,
        price REAL NOT NULL,
		weight TEXT NOT NULL,
		
		type1 TEXT NOT NULL,
		type2 TEXT NOT NULL,
		date TEXT,
		fromplace TEXT NOT NULL,
		quantity INTEGER NOT NULL,
		salesvolume INTEGER NOT NULL,
		brand TEXT NOT NULL,
		shop TEXT NOT NULL,

		reviewsnumber INTEGER DEFAULT 0,
		reviewsgrade REAL DEFAULT 0,
		title TEXT NOT NULL
    );`
	_, err = db.Exec(createProductTableQuery)
	if err != nil {
		log.Fatal("Failed to create products table:", err)
	}

	// ==== 收藏表实现参考购物车那俩表 ====
	// 创建收藏表，确保外键约束有效
	createFavoriteTableQuery := `
    CREATE TABLE IF NOT EXISTS favorites (
		user_id INTEGER,
		
		FOREIGN KEY(user_id) REFERENCES users(id),
        PRIMARY KEY(user_id)
    );`
	_, err = db.Exec(createFavoriteTableQuery)
	if err != nil {
		log.Fatal("Failed to create favorites table:", err)
	}

	// 创建商品收藏表，用户和商品是1对多的关系
	createFavoriteItemTableQuery := `
    CREATE TABLE IF NOT EXISTS favorite_items(
	    favorite_id INTEGER,
		product_id INTEGER,
		collectdate TIMESTAMP,

		FOREIGN KEY(favorite_id) REFERENCES favorites(user_id),
		FOREIGN KEY(product_id) REFERENCES products(id),
        PRIMARY KEY(favorite_id, product_id)
    );`
	_, err = db.Exec(createFavoriteItemTableQuery)
	if err != nil {
		log.Fatal("Failed to create favorite_items table:", err)
	}

	// 创建购物车表，确保外键约束有效
	createCartTableQuery := `
    CREATE TABLE IF NOT EXISTS carts (
        user_id INTEGER,
		total_amount REAL DEFAULT 0,

        FOREIGN KEY(user_id) REFERENCES users(id),
        PRIMARY KEY(user_id)
    );`
	_, err = db.Exec(createCartTableQuery)
	if err != nil {
		log.Fatal("Failed to create carts table:", err)
	}

	// 创建购物车商品表：购物车和商品是1对多的关系
	createCartItemTableQuery := `
    CREATE TABLE IF NOT EXISTS cart_items (
        cart_id INTEGER,
        product_id INTEGER,
        quantity INTEGER,
        price REAL,
		status TEXT DEFAULT '已支付',

        FOREIGN KEY(cart_id) REFERENCES carts(user_id),
        FOREIGN KEY(product_id) REFERENCES products(id),
        PRIMARY KEY(cart_id, product_id)
    );`
	_, err = db.Exec(createCartItemTableQuery)
	if err != nil {
		log.Fatal("Failed to create cart_items table:", err)
	}

	// 创建订单表
	createOrderTableQuery := `
    CREATE TABLE IF NOT EXISTS orders (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        order_date TIMESTAMP,
        status TEXT DEFAULT '未支付',

        FOREIGN KEY(user_id) REFERENCES users(id)
    );`
	_, err = db.Exec(createOrderTableQuery)
	if err != nil {
		log.Fatal("Failed to create orders table:", err)
	}

	// 创建订单商品表：订单和商品是1对多的关系
	createOrderItemTableQuery := `
    CREATE TABLE IF NOT EXISTS order_items (
        order_id INTEGER NOT NULL,
        product_id INTEGER NOT NULL,
        quantity INTEGER NOT NULL,
        price REAL NOT NULL,
        status TEXT NOT NULL,
        is_commented BOOLEAN DEFAULT FALSE,

        FOREIGN KEY (order_id) REFERENCES orders(id),
        FOREIGN KEY (product_id) REFERENCES products(id),
        PRIMARY KEY (order_id, product_id)
    );`
	_, err = db.Exec(createOrderItemTableQuery)
	if err != nil {
		log.Fatal("Failed to create order_items table:", err)
	}

	// 创建评论表
	createCommentTableQuery := `
    CREATE TABLE IF NOT EXISTS comments (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        product_id INTEGER NOT NULL,
        user_id INTEGER NOT NULL,
        order_id INTEGER NOT NULL,
        content TEXT NOT NULL,
        rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

        FOREIGN KEY (product_id) REFERENCES products(id),
        FOREIGN KEY (user_id) REFERENCES users(id),
        FOREIGN KEY (order_id) REFERENCES orders(id)
    );`
	_, err = db.Exec(createCommentTableQuery)
	if err != nil {
		log.Fatal("Failed to create comments table:", err)
	}

	// 创建评论触发器
	_, err = db.Exec(`
		CREATE TRIGGER IF NOT EXISTS update_product_rating_after_insert
		AFTER INSERT ON comments
		BEGIN
			UPDATE products 
			SET reviewsgrade = (
				SELECT AVG(rating) 
				FROM comments 
				WHERE product_id = NEW.product_id
			),
			reviewsnumber = (
				SELECT COUNT(*) 
				FROM comments 
				WHERE product_id = NEW.product_id
			)
			WHERE id = NEW.product_id;
		END;`)
	if err != nil {
		log.Fatal("Failed to create insert trigger:", err)
	}

	// 创建删除评论的触发器
	_, err = db.Exec(`
		CREATE TRIGGER IF NOT EXISTS update_product_rating_after_delete
		AFTER DELETE ON comments
		BEGIN
			UPDATE products 
			SET reviewsgrade = COALESCE(
				(SELECT AVG(rating) 
				FROM comments 
				WHERE product_id = OLD.product_id),
				0
			),
			reviewsnumber = (
				SELECT COUNT(*) 
				FROM comments 
				WHERE product_id = OLD.product_id
			)
			WHERE id = OLD.product_id;
		END;`)
	if err != nil {
		log.Fatal("Failed to create delete trigger:", err)
	}

	log.Println("Database initialized successfully.")
}

// 关闭数据库连接
func CloseDB() {
	if err := db.Close(); err != nil {
		log.Fatal("Failed to close database:", err)
	}
	log.Println("Database connection closed.")
}

// GetDB 返回数据库连接实例
func GetDB() *sql.DB {
	return db
}